 using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections.Generic;

namespace DAL
{

    #region Enums
    public enum Sort
    {
        Asc,
        Desc
    }

    public enum Comparison
    {
        Equal,
        GreaterThan,
        GreaterThanOrEqual,
        LessThan,
        LessThanOrEqual,
        NotEqual,
        Like,
        Contains
    }
    #endregion

    public abstract class DataManager<RowType>
        where RowType : DataManagerRow, new()
    {
        #region Sql Parameters
       
        private void PopCommandParams(SqlCommand comm, DataManagerQuery query)
        {
            foreach (DataManagerQuery.Param param in query.Params)
            {
                Type paramType = param.GetType();
                SqlDbType valueType = SqlDbType.Variant;

                if (paramType == typeof(int))
                {
                    valueType = SqlDbType.Int;
                }
                else if (paramType == typeof(bool))
                {
                    valueType = SqlDbType.Bit;
                }
                else if (paramType == typeof(string) || paramType == typeof(char))
                {
                    valueType = SqlDbType.VarChar;
                }
                else if (paramType == typeof(DateTime))
                {
                    valueType = SqlDbType.DateTime;
                }
                else if (paramType == typeof(long))
                {
                    valueType = SqlDbType.BigInt;
                }
                else if (paramType == typeof(float) || paramType == typeof(double))
                {
                    valueType = SqlDbType.Float;
                }
                else if (paramType == typeof(short))
                {
                    valueType = SqlDbType.SmallInt;
                }
                else if (paramType == typeof(byte))
                {
                    valueType = SqlDbType.TinyInt;
                }
                
                
                if (valueType == SqlDbType.Variant)
                {
                    comm.Parameters.AddWithValue(param.ParameterName, param.Value);
                }
                else if (valueType == SqlDbType.VarChar)
                {
                    if (param.Value.ToString().Length < 8000)
                    {
                        comm.Parameters.Add(param.ParameterName, SqlDbType.VarChar, param.Value.ToString().Length).Value = param.Value;
                    }
                    else
                    {
                        comm.Parameters.Add(param.ParameterName, SqlDbType.Text).Value = param.Value;
                    }
                }
                else
                {
                    comm.Parameters.Add(param.ParameterName, valueType).Value = param.Value;
                }
            }
            //Params.Clear();
        }
#endregion

        #region Connection
        public virtual SqlConnection Connection
        {
            get
            {
                if (String.IsNullOrEmpty(DataManagerConnection.Connection.ConnectionString))
                {
                    DataManagerConnection.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["DataManagerConnectionString"].ConnectionString;
                }
                return DataManagerConnection.Connection;
            }
        }
        #endregion

        #region Database Actions
        private List<RowType> GetItems(DataManagerQuery query, string sqlquery)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sqlquery, Connection);
            PopCommandParams(adapter.SelectCommand, query);
            DataTable table = new DataTable();
            adapter.Fill(table);

            List<RowType> itemList = new List<RowType>(table.Rows.Count);
            FillItemList(itemList, table);

            table.Dispose();
            adapter.Dispose();

            return itemList;        }

        private void FillItemList(List<RowType> itemList, DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                //RowType Item;
                //if (!Pool.TryGetValue(Convert.ToInt64(row["ID"]), out Item))
                long id = Convert.ToInt64(row["ID"]);
                RowType Item = (RowType)ObjectPool.Item(id);
                if (Item == null)
                {
                    // Item not found in object pool
                    Item = new RowType();
                    Item.ID = id;
                    Item.ExistsInTable = true;
                    ObjectPool.TryAdd(Item.ID, Item);

                    RowType obj = Item;
                    lock (obj)
                    {
                        CopyToItem(row, Item);
                    }
                    Item.Initialized = true;
                }
                itemList.Add(Item);
            }
        }

        public List<RowType> GetItems()
        {
            return GetItems(new DataManagerQuery());
        }

        public List<RowType> GetItems(DataManagerQuery query)
        {
            return GetItems(query, BuildSqlSelectItems(query));
        }

        /// <summary>
        /// Returned a range of records on a particular page.
        /// </summary>
        /// <param name="startPage">The starting page of the result set.</param>
        /// <param name="rowsPerPage">The number of records on each page of the result set.</param>
        /// <param name="pageByUniqueKey">This method uses two different sorting techniques: one
        /// algorithm that sorts efficiently on unique keys, and another algorithm that can sort
        /// results on fields with duplicate entries. If the first field in your Order By clause
        /// is a unique key, such as an ID field, assign pageByUniqueKey = true, otherwise 
        /// pageByUniqueKey = false.
        /// </param>
        /// <returns></returns>
        public DataManagerPage<RowType> GetItemsByPage(DataManagerQuery query, int startPage, int rowsPerPage, bool pageByUniqueKey)
        {
            if (startPage <= 0)
            {
                startPage = 1;
            }
            if (rowsPerPage <= 0)
            {
                rowsPerPage = 1;
            }

            SqlCommand comm;
            if (pageByUniqueKey)
            {
                // this paging technique is optimized for unique keys; that is, when the first
                // field in the Order By clause is a unique key (such as an ID column).
                comm = new SqlCommand(BuildPagedSqlSelect_Unique(query, startPage, rowsPerPage));
            }
            else
            {
                // this paging technique is not optimized, but it will page any recordset,
                // even when the first field in the Order By clause is non-unique (such as
                // a Birthday field).
                comm = new SqlCommand(BuildPagedSqlSelect_NonUnique(query, startPage, rowsPerPage));
            }

            SqlParameter totalPages = new SqlParameter();
            totalPages.ParameterName = "@totalpages";
            totalPages.SqlDbType = SqlDbType.Int;
            totalPages.Direction = ParameterDirection.Output;
            comm.Parameters.Add(totalPages);
            PopCommandParams(comm, query);

            List<RowType> myItems = GetBySqlCommand(comm);
            int pageCount = Convert.ToInt32(comm.Parameters["@totalpages"].Value);

            return new DataManagerPage<RowType>(pageCount, myItems);
        }

        public DataManagerPage<RowType> GetItemsByPage(DataManagerQuery query, int startPage, int rowsPerPage)
        {
            return GetItemsByPage(query, startPage, rowsPerPage, true);
        }


        /// <summary>
        /// GetBySqlCommand should only be used for very complicated selects, or when the DataManager
        /// functions are inadequate. Any other time, use GetItems() as the preferred method of retrieving
        /// data from the database.
        /// 
        /// Note 1: If you choose not to specify an SqlConnection, DAL.DataManagerConnection.Connection
        /// will be used as the default connection string.
        /// 
        /// Note 2: Always use parameterized queries. Use the SqlCommand.Parameters.Add or
        /// SqlCommand.Parameters.AddWithValue to add values to your command object.
        /// </summary>
        /// <param name="sqlquery">An SQL select statement to execute.</param>
        /// <returns>A list of row objects.</returns>
        public List<RowType> GetBySqlCommand(SqlCommand comm)
        {
            /* Rather than making the GetItems(string sqlquery) method public, 
             * I've deliberately created a seperate GetBySqlQuery(string sqlquery) method 
             * to discourage dependence on sql queries.
             * 
             * The DataManager exists so that it can write queries for programmers and 
             * promote data abstraction. However, in my experience as a programmer, I've 
             * needed to write some complicated queries (such as a query for searching
             * posts in a messageboard); in those cases, its ok to fall back
             * on the GetBySqlCommand method when the intrinsic DataManager functions aren't
             * flexible enough.
             */

            if (comm.Connection == null)
            {
                comm.Connection = DAL.DataManagerConnection.Connection;
            }

            SqlDataAdapter adapter = new SqlDataAdapter(comm);
            DataTable table = new DataTable();
            adapter.Fill(table);

            List<RowType> itemList = new List<RowType>(table.Rows.Count);
            FillItemList(itemList, table);
            table.Dispose();
            adapter.Dispose();

            return itemList;
        }

        public RowType GetItem(long id)
        {
            // checking to see if object already exists in pool to avoid hitting the database
            RowType Item = (RowType)ObjectPool.Item(id);
            if (Item != null && ObjectPool.IsAlive(id))
            {
                return Item;
            }
            else
            {
                DataManagerQuery query = new DataManagerQuery();
                query.AddParam("ID", id);
                return GetItem(query);
            }
        }

        public RowType GetItem(DataManagerQuery query)
        {
            SqlCommand comm = new SqlCommand(BuildSqlSelectItems(query).Replace("SELECT *", "SELECT TOP 1 *"));
            PopCommandParams(comm, query);
            List<RowType> myItems = GetBySqlCommand(comm);
            return GetFirstItem(myItems);
        }

        private RowType GetFirstItem(List<RowType> items)
        {
            if (items != null && items.Count > 0)
            {
                return items[0];
            }
            else
            {
                return null;
            }
        }

        public int GetCount()
        {
            return GetCount(new DataManagerQuery());
        }

        public int GetCount(DataManagerQuery query)
        {
            query.OrderBys.Clear();
            string sqlQuery = BuildSqlSelectItems(query).Replace("SELECT *", "SELECT Count(*)");
            SqlCommand comm = new SqlCommand(sqlQuery, DAL.DataManagerConnection.Connection);
            PopCommandParams(comm, query);

            int result = 0;

            try
            {
                comm.Connection.Open();
                result = Convert.ToInt32(comm.ExecuteScalar());
            }
            finally
            {
                comm.Connection.Close();
            }
            return result;
        }

        public void Insert(RowType Item)
        {
            if (!Item.ExistsInTable)
            {
                DataManagerQuery query = new DataManagerQuery();
                CopyFromItem(query, Item);
                SqlCommand InsertCommand = new SqlCommand(BuildSqlInsertItem(query), DataManagerConnection.Connection);
                PopCommandParams(InsertCommand, query);
                try
                {
                    InsertCommand.Connection.Open();

                    SqlParameter retval = new SqlParameter();
                    retval.ParameterName = "@retval";
                    retval.Direction = ParameterDirection.Output;
                    retval.DbType = DbType.Int64;
                    InsertCommand.Parameters.Add(retval);

                    InsertCommand.ExecuteNonQuery();
                    Item.ID = Convert.ToInt64(InsertCommand.Parameters["@retval"].Value);
                    Item.ExistsInTable = true;
                    Item.Initialized = true;
                    ObjectPool.TryAdd(Item.ID, Item);
                }
                finally
                {
                    InsertCommand.Connection.Close();
                }
            }
            else
            {
                throw new Exception("Item already exists in the database.");
            }
        }

        public void Update(RowType Item)
        {
            if (Item.ExistsInTable)
            {
                if (Item.Initialized)
                {
                    DataManagerQuery query = new DataManagerQuery();
                    CopyFromItem(query, Item);
                    SqlCommand UpdateCommand = new SqlCommand(BuildSqlUpdateItem(query), DataManagerConnection.Connection);
                    PopCommandParams(UpdateCommand, query);
                    try
                    {
                        UpdateCommand.Connection.Open();
                        UpdateCommand.Parameters.AddWithValue("ID", Item.ID);
                        UpdateCommand.ExecuteNonQuery();
                    }
                    finally
                    {
                        UpdateCommand.Connection.Close();
                    }
                }
                else
                {
                    throw new Exception("Cannot update an uninitialized item.");
                }
            }
            else
            {
                throw new Exception("Cannot update item until it has been added to the table.");
            }
        }


        public void Delete(RowType Item)
        {
            SqlCommand DeleteCommand = new SqlCommand(BuildSqlDeleteItem(), DataManagerConnection.Connection);
            try
            {
                DeleteCommand.Connection.Open();
                DeleteCommand.Parameters.AddWithValue("ID", Item.ID);
                DeleteCommand.ExecuteNonQuery();
                ObjectPool.TryRemove(Item.ID);
                Item.ID = 0;
                Item.ExistsInTable = false;
                Item = null;
            }
            finally
            {
                DeleteCommand.Connection.Close();
            }
        }

        public int ExecuteNonQuery(string sql)
        {
            SqlCommand comm = new SqlCommand(sql);
            return ExecuteNonQuery(comm);
        }

        public int ExecuteNonQuery(SqlCommand comm)
        {
            try
            {
                comm.Connection = DataManagerConnection.Connection;
                comm.Connection.Open();
                return comm.ExecuteNonQuery();
            }
            finally
            {
                comm.Connection.Close();
            }
        }

#endregion

        #region sqlstatements

        private string BuildSqlSelectItems(DataManagerQuery query)
        {
            System.Text.StringBuilder sBuilder = new System.Text.StringBuilder();
            sBuilder.AppendFormat("SELECT * FROM [{0}]", TableName).AppendLine();

            WhereClause(query, sBuilder);
            OrderByClause(query, sBuilder);
            //OrderBys.Clear();

            return sBuilder.ToString();
        }

        private string BuildPagedSqlSelect_Unique(DataManagerQuery query, int startpage, int rowsperpage)
        {
            System.Text.StringBuilder sBuilder = new System.Text.StringBuilder();

            // This paging method doesn't work when sorting on fields with duplicate entries.
            // I wrote a post about this technique here:
            //      http://forums.devshed.com/ms-sql-development-95/paging-large-recordsets-483130.html

            // setting up my variables
            sBuilder.AppendFormat("declare @startpage int; set @startpage = {0}", startpage).AppendLine();
            sBuilder.AppendFormat("declare @maximumrows int; set @maximumrows = {0}", rowsperpage).AppendLine();
            sBuilder.AppendLine("declare @startrow int; set @startrow = ((@startpage - 1) * @maximumrows) + 1");

            // geting a page count
            BuildTotalPages(query, sBuilder, rowsperpage);

            // getting the first id for the page
            sBuilder.AppendLine("SET ROWCOUNT @startrow");
            sBuilder.AppendLine("declare @startvalue sql_variant");

            // selecting the latest value of my keyfield
            if (query.OrderBys.Count == 0)
            {
                query.AddSort("ID", Sort.Asc);
            }
            string keyfield = "";
            string keydirection = "";

            DataManagerQuery.OrderBy mySort = query.OrderBys[0];
            keyfield = mySort.Param;
            if (mySort.Direction == Sort.Asc)
            {
                keydirection = ">=";
            }
            else
            {
                keydirection = "<=";
            }
        
            sBuilder.AppendFormat("SELECT @startvalue = [{0}] FROM [{1}]", keyfield, TableName).AppendLine();
            WhereClause(query, sBuilder);
            OrderByClause(query, sBuilder);


            // selecting a page of data from the database
            sBuilder.AppendLine();
            sBuilder.AppendLine("SET ROWCOUNT @maximumrows");
            sBuilder.AppendFormat("SELECT * FROM [{0}]", TableName).AppendLine();
            WhereClause(query, sBuilder);
            if (query.Params.Count == 0)
            {
                // adding a where clause if one doesn't already exist
                sBuilder.AppendLine("WHERE");
            }
            else
            {
                // if a where clause already exists, add an "AND"
                sBuilder.AppendLine("AND");
            }
            sBuilder.AppendFormat("[{0}] {1} @startvalue", keyfield, keydirection).AppendLine();

            OrderByClause(query, sBuilder);
            //OrderBys.Clear();

            sBuilder.AppendLine();
            sBuilder.AppendLine("SET ROWCOUNT 0");

            return sBuilder.ToString();
        }

        private void BuildTotalPages(DataManagerQuery query, System.Text.StringBuilder sBuilder, int rowsperpage)
        {
            sBuilder.AppendFormat("SELECT @totalpages = Ceiling(Count(*) / {1}.0) FROM {0}", TableName, rowsperpage).AppendLine();
            WhereClause(query, sBuilder);
        }

        private string BuildPagedSqlSelect_NonUnique(DataManagerQuery query, int startpage, int rowsperpage)
        {
            System.Text.StringBuilder sBuilder = new System.Text.StringBuilder();

            
            if (query.OrderBys.Count == 0)
            {
                query.AddSort("ID", Sort.Asc);
            }
            DataManagerQuery.OrderBy keyField = query.OrderBys[0];

            // geting a page count
            BuildTotalPages(query, sBuilder, rowsperpage);

            if (startpage == 1)
            {
                string sqlQuery = BuildSqlSelectItems(query);
                sqlQuery = sqlQuery.Replace("SELECT *",
                    String.Format("SELECT TOP {0} *", rowsperpage));
                sBuilder.AppendLine(sqlQuery);
            }
            else
            {
                sBuilder.AppendLine("SELECT * FROM (");
                sBuilder.AppendFormat("SELECT Top {0} * FROM (", rowsperpage).AppendLine();
                sBuilder.AppendFormat("SELECT Top {1} * FROM [{0}]", TableName, startpage * rowsperpage);
                WhereClause(query, sBuilder);
                OrderByClause(query, sBuilder);
                sBuilder.AppendLine();
                sBuilder.AppendLine(") inner_select ORDER BY ");
                if (keyField.Direction == Sort.Asc)
                {
                    sBuilder.AppendFormat("[{0}] DESC", keyField.Param).AppendLine();
                }
                else
                {
                    sBuilder.AppendFormat("[{0}] ASC", keyField.Param).AppendLine();
                }
                sBuilder.AppendFormat(") middle_select ORDER BY {0}", keyField.ToString());
            }

            return sBuilder.ToString();
        }

        private void WhereClause(DataManagerQuery query, System.Text.StringBuilder sBuilder)
        {
            if (query.Params.Count > 0)
            {
                sBuilder.AppendLine("WHERE");
                for (int i = 0; i <= query.Params.Count - 1; i++)
                {
                    sBuilder.AppendLine(query.Params[i].ToString());
                    if (i < query.Params.Count - 1)
                    {
                        sBuilder.Append(" AND ");
                    }
                }
            }
        }

        private void OrderByClause(DataManagerQuery query, System.Text.StringBuilder sBuilder)
        {
            if (query.OrderBys.Count > 0)
            {
                sBuilder.AppendLine("ORDER BY");
                for (int i = 0; i <= query.OrderBys.Count - 1; i++)
                {
                    sBuilder.Append(query.OrderBys[i].ToString());
                    if (i < query.OrderBys.Count - 1)
                    {
                        sBuilder.Append(", ");
                    }
                }
            }
        }

        private string BuildSqlInsertItem(DataManagerQuery query)
        {
            System.Text.StringBuilder sBuilder = new System.Text.StringBuilder();
            sBuilder.AppendFormat("INSERT INTO [{0}]", TableName).AppendLine();

            sBuilder.AppendLine("(");
            for (int i = 0; i <= query.Params.Count - 1; i++)
            {
                sBuilder.AppendFormat("[{0}]", query.Params[i].ColumnName).AppendLine();
                if (i < query.Params.Count - 1)
                {
                    sBuilder.AppendLine(",");
                }
            }
            sBuilder.AppendLine(")");


            sBuilder.AppendLine("VALUES (");
            for (int i = 0; i <= query.Params.Count - 1; i++)
            {
                sBuilder.AppendFormat("@{0}", query.Params[i].ParameterName).AppendLine();
                if (i < query.Params.Count - 1)
                {
                    sBuilder.AppendLine(",");
                }
            }
            sBuilder.AppendLine("); SELECT @retval = scope_identity();");

            return sBuilder.ToString();
        }

        private string BuildSqlUpdateItem(DataManagerQuery query)
        {
            System.Text.StringBuilder sBuilder = new System.Text.StringBuilder();
            sBuilder.AppendFormat("UPDATE [{0}]", TableName).AppendLine();

            sBuilder.AppendLine("SET ");
            for (int i = 0; i <= query.Params.Count - 1; i++)
            {
                sBuilder.AppendFormat("[{0}]", query.Params[i].ColumnName);
                sBuilder.AppendFormat("=@{0}", query.Params[i].ParameterName).AppendLine();
                if (i < query.Params.Count - 1)
                {
                    sBuilder.Append(',');
                }
            }
            sBuilder.AppendLine("WHERE [ID]=@ID");
            return sBuilder.ToString();
        }

        private string BuildSqlDeleteItem()
        {
            return string.Format("DELETE FROM [{0}] WHERE ID = @ID", TableName);
        }
        #endregion


        public Pool ObjectPool = new Pool();

        public abstract string TableName{get;}
        protected abstract void CopyToItem(DataRow Row, RowType Item);
        protected abstract void CopyFromItem(DataManagerQuery query, RowType Item);
        
    }
}